package com.mcp.converter.application.service;

import com.mcp.converter.domain.checker.CompatibilityChecker;
import com.mcp.converter.domain.checker.CompatibilityResult;
import com.mcp.converter.shared.response.analysis.BatchAnalysisResponse;
import com.mcp.converter.shared.response.sql.SQLParseResult;
import com.mcp.converter.domain.parser.SQLParser;
import com.mcp.converter.domain.report.IncompatibilityReport;
import com.mcp.converter.domain.report.ReportGenerator;
import com.mcp.converter.domain.rule.manager.RuleManager;
import com.mcp.converter.shared.enums.IncompatibilityType;
import com.mcp.converter.shared.response.analysis.AnalysisResponse;
import com.mcp.converter.shared.exception.SqlAnalysisException;
import com.mcp.converter.shared.response.sql.SqlExtractionResult;
import lombok.extern.slf4j.Slf4j;
import org.springframework.cache.annotation.Cacheable;
import org.springframework.stereotype.Service;
import com.fasterxml.jackson.databind.JsonNode;
import com.fasterxml.jackson.databind.ObjectMapper;
import com.fasterxml.jackson.databind.node.ObjectNode;

import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.List;
import java.util.Set;

/**
 * SQL分析服务实现类
 * 提供SQL语句的兼容性分析功能
 * @author zengxiong
 */
@Slf4j
@Service
public class SqlAnalysisService {

    private final SQLParser sqlParser;
    private final CompatibilityChecker compatibilityChecker;
    private final ReportGenerator reportGenerator;
    private final RuleManager ruleManager;
    private final ObjectMapper objectMapper;

    public SqlAnalysisService(SQLParser sqlParser, CompatibilityChecker compatibilityChecker, ReportGenerator reportGenerator, RuleManager ruleManager) {
        this.sqlParser = sqlParser;
        this.compatibilityChecker = compatibilityChecker;
        this.reportGenerator = reportGenerator;
        this.ruleManager = ruleManager;
        this.objectMapper = new ObjectMapper();
    }

    /**
     * 分析SQL兼容性
     * 
     * @param sql SQL语句
     * @return 分析结果
     */
    @Cacheable(value = "sqlAnalysis", key = "#sql.hashCode()")
    public AnalysisResponse analyze(String sql) {
        if (sql == null || sql.trim().isEmpty()) {
            throw new SqlAnalysisException("SQL语句不能为空");
        }

        try {
            log.debug("开始分析SQL: {}", sql);
            
            // 1. 解析SQL
            SQLParseResult parseResult = sqlParser.parse(sql);
            log.debug("SQL解析完成，元素数量: {}", parseResult.getElements().size());
            
            // 2. 兼容性检查
            CompatibilityResult compatibilityResult = compatibilityChecker.check(parseResult);
            log.debug("兼容性检查完成，是否兼容: {}, 不兼容点数量: {}", 
                     compatibilityResult.isCompatible(), 
                     compatibilityResult.getIncompatiblePoints().size());
            
            // 3. 生成报告
            IncompatibilityReport report = reportGenerator.generate(compatibilityResult, null);
            log.debug("报告生成完成，报告ID: {}", report.getReportId());
            
            // 4. 构建响应
            AnalysisResponse response = buildAnalysisResponse(sql, compatibilityResult, report);
            
            // 5. 如果解析失败但检测到不兼容函数，添加解析失败的详情
            if (parseResult.getAttribute("parseFailure") != null && (Boolean) parseResult.getAttribute("parseFailure")) {
                response.addIncompatibilityDetail(new AnalysisResponse.IncompatibilityDetail() {{
                    setType("PARSE_FAILURE");
                    setElement("SQL_PARSER");
                    setReason("SQL解析失败，使用后备检测: " + parseResult.getAttribute("parseError"));
                    setSuggestion("建议简化SQL语法或去除不支持的MyBatis标签");
                    setPosition("未知");
                }});
                log.info("检测到SQL解析失败但包含不兼容函数的情况");
            }
            
            return response;
            
        } catch (Exception e) {
            log.error("分析SQL时发生错误: {}", e.getMessage(), e);
            throw new SqlAnalysisException("SQL分析失败: " + e.getMessage(), e);
        }
    }

    /**
     * 批量分析SQL
     * 
     * @param sqlList SQL列表
     * @return 批量分析结果
     */
    public BatchAnalysisResponse analyzeBatch(List<String> sqlList) {
        if (sqlList == null || sqlList.isEmpty()) {
            throw new SqlAnalysisException("SQL列表不能为空");
        }

        log.info("开始批量分析，SQL数量: {}", sqlList.size());
        
        BatchAnalysisResponse response = new BatchAnalysisResponse();
        response.setTotalCount(sqlList.size());
        
        for (int i = 0; i < sqlList.size(); i++) {
            try {
                String sql = sqlList.get(i);
                AnalysisResponse result = analyze(sql);
                response.addResult(i, result);
                
                if (result.isCompatible()) {
                    response.incrementCompatibleCount();
                } else {
                    response.incrementIncompatibleCount();
                }
                
            } catch (Exception e) {
                // 改进的错误处理：先尝试后备兼容性检测
                String sql = sqlList.get(i);
                AnalysisResponse fallbackResult = performFallbackAnalysis(sql, e);
                
                if (fallbackResult != null) {
                    // 后备分析成功，将结果分类为兼容或不兼容
                    response.addResult(i, fallbackResult);
                    if (fallbackResult.isCompatible()) {
                        response.incrementCompatibleCount();
                    } else {
                        response.incrementIncompatibleCount();
                    }
                } else {
                    // 后备分析也失败，才标记为错误
                    log.warn("批量分析中第{}个SQL失败: {}", i + 1, e.getMessage());
                    response.addError(i, e.getMessage());
                    response.incrementErrorCount();
                }
            }
        }
        
        log.info("批量分析完成，兼容: {}, 不兼容: {}, 错误: {}", 
                response.getCompatibleCount(), 
                response.getIncompatibleCount(), 
                response.getErrorCount());
        
        return response;
    }

    /**
     * 后备分析：当正常分析失败时，尝试通过正则表达式检测不兼容函数
     * 
     * @param sql SQL语句
     * @param originalException 原始异常
     * @return 后备分析结果，如果无法分析则返回null
     */
    private AnalysisResponse performFallbackAnalysis(String sql, Exception originalException) {
        try {
            log.debug("开始后备分析SQL: {}", sql);
            
            // 检查SQL是否具有有效的基本结构
            if (!isValidSqlStructure(sql)) {
                log.debug("SQL结构无效，跳过后备分析");
                return null;
            }
            
            // 直接检测SQL中的不兼容函数
            boolean hasIncompatibleFunctions = containsIncompatibleFunctions(sql);
            
            if (hasIncompatibleFunctions) {
                log.debug("检测到不兼容函数，构建不兼容结果");
                // 构建不兼容的分析结果
                AnalysisResponse response = new AnalysisResponse();
                response.setOriginalSql(sql);
                response.setCompatible(false);
                response.setIncompatiblePointCount(1);
                response.setReportId("FALLBACK-" + System.currentTimeMillis());
                
                // 添加不兼容详情
                AnalysisResponse.IncompatibilityDetail detail = new AnalysisResponse.IncompatibilityDetail();
                detail.setType("FUNCTION");
                detail.setElement("ORACLE_SPECIFIC_FUNCTION");
                detail.setReason("SQL包含Oracle特有函数，OceanBase可能不支持");
                detail.setSuggestion("建议使用OceanBase兼容的函数替代或在应用层处理");
                detail.setPosition("解析失败，无法确定具体位置");
                response.addIncompatibilityDetail(detail);
                
                return response;
            } else {
                log.debug("未检测到不兼容函数，构建兼容结果");
                // 如果没有不兼容函数且SQL结构有效，则认为是兼容的
                AnalysisResponse response = new AnalysisResponse();
                response.setOriginalSql(sql);
                response.setCompatible(true);
                response.setIncompatiblePointCount(0);
                response.setReportId("FALLBACK-COMPATIBLE-" + System.currentTimeMillis());
                
                return response;
            }
            
        } catch (Exception e) {
            log.debug("后备分析失败: {}", e.getMessage());
        }
        
        return null;
    }
    
    /**
     * 检查SQL是否具有有效的基本结构
     */
    private boolean isValidSqlStructure(String sql) {
        if (sql == null || sql.trim().isEmpty()) {
            return false;
        }
        
        String upperSql = sql.trim().toUpperCase();
        
        // 检查是否包含基本的SQL关键字结构
        return (upperSql.startsWith("SELECT") && upperSql.contains("FROM")) ||
               (upperSql.startsWith("INSERT") && upperSql.contains("INTO")) ||
               (upperSql.startsWith("UPDATE") && upperSql.contains("SET")) ||
               (upperSql.startsWith("DELETE") && upperSql.contains("FROM")) ||
               upperSql.startsWith("CREATE") ||
               upperSql.startsWith("ALTER") ||
               upperSql.startsWith("DROP");
    }
    
    /**
     * 检测SQL中是否包含不兼容函数
     * @param sql SQL语句
     * @return 是否包含不兼容函数
     */
    private boolean containsIncompatibleFunctions(String sql) {
        if (sql == null || sql.trim().isEmpty()) {
            return false;
        }
        
        // 获取所有不兼容函数规则
        Set<String> incompatibleFunctions = ruleManager.getAllIncompatibleFunctions();
        
        for (String function : incompatibleFunctions) {
            // 使用函数调用模式匹配，避免误报
            String pattern = "\\b" + function + "\\s*\\(";
            java.util.regex.Pattern compiledPattern = java.util.regex.Pattern.compile(pattern, java.util.regex.Pattern.CASE_INSENSITIVE);
            java.util.regex.Matcher matcher = compiledPattern.matcher(sql);
            
            if (matcher.find()) {
                return true;
            }
        }
        
        return false;
    }

    /**
     * 构建分析响应
     */
    private AnalysisResponse buildAnalysisResponse(String sql, 
                                                  CompatibilityResult compatibilityResult, 
                                                  IncompatibilityReport report) {
        AnalysisResponse response = new AnalysisResponse();
        response.setOriginalSql(sql);
        response.setCompatible(compatibilityResult.isCompatible());
        response.setIncompatiblePointCount(compatibilityResult.getIncompatiblePointCount());
        response.setReportId(report.getReportId());
        
        // 添加不兼容点详情
        if (!compatibilityResult.isCompatible()) {
            compatibilityResult.getIncompatiblePoints().forEach(point -> {
                AnalysisResponse.IncompatibilityDetail detail = new AnalysisResponse.IncompatibilityDetail();
                detail.setType(point.getType().name());
                detail.setElement(point.getElement() != null ? point.getElement().getName() : point.getName());
                detail.setReason(getReasonFromType(point.getType()));
                detail.setSuggestion(getSuggestionFromType(point.getType()));
                detail.setPosition(getPositionInfo(point));
                response.addIncompatibilityDetail(detail);
            });
        }
        
        return response;
    }

    /**
     * 获取位置信息
     */
    private String getPositionInfo(com.mcp.converter.domain.checker.IncompatiblePoint point) {
        if (point.getElement() != null && point.getElement().getPosition() != null) {
            var position = point.getElement().getPosition();
            return String.format("行%d, 列%d", position.getLine(), position.getColumn());
        } else if (point.getPosition() != null) {
            return String.format("行%d, 列%d", point.getPosition().getLine(), point.getPosition().getColumn());
        }
        return "未知";
    }

    /**
     * 根据不兼容类型获取原因
     */
    private String getReasonFromType(IncompatibilityType type) {
        switch (type) {
            case DATA_TYPE:
                return "数据类型不兼容";
            case FUNCTION:
                return "函数不兼容或不支持";
            case SYNTAX:
                return "语法结构不兼容";
            default:
                return "未知的不兼容类型";
        }
    }

    /**
     * 根据不兼容类型获取建议
     */
    private String getSuggestionFromType(IncompatibilityType type) {
        switch (type) {
            case DATA_TYPE:
                return "建议修改为OceanBase支持的数据类型";
            case FUNCTION:
                return "建议使用OceanBase兼容的函数替代";
            case SYNTAX:
                return "建议修改为OceanBase支持的语法结构";
            default:
                return "建议查阅OceanBase兼容性文档";
        }
    }

    /**
     * 智能SQL提取和校验
     */
    public SqlExtractionResult extractAndValidateSqls(JsonNode extractNode, ObjectMapper mapper) {
        List<String> validSqls = new ArrayList<>();
        List<JsonNode> sqlDetailsList = new ArrayList<>();
        JsonNode sqlStatements = extractNode.get("sqlStatements");
        
        if (sqlStatements != null) {
            for (JsonNode sqlNode : sqlStatements) {
                // 安全地获取字段值，避免空指针异常
                String sqlId = getTextValue(sqlNode, "sqlId", "");
                String sqlType = getTextValue(sqlNode, "sqlType", "");
                String originalSql = getTextValue(sqlNode, "originalSql", "");
                String standardSql = getTextValue(sqlNode, "fixedSql", ""); // 使用 fixedSql 字段
                boolean isComplete = getBooleanValue(sqlNode, "isComplete", false);
                int lineNumber = getIntValue(sqlNode, "startLine", 0); // 使用 startLine 字段
                
                if (isComplete && standardSql != null && !standardSql.trim().isEmpty()) {
                    validSqls.add(standardSql);
                }
                
                // 构建SQL详情
                ObjectNode detailNode = mapper.createObjectNode();
                detailNode.put("sqlId", sqlId);
                detailNode.put("sqlType", sqlType);
                detailNode.put("lineNumber", lineNumber);
                detailNode.put("originalSql", originalSql);
                detailNode.put("standardSql", standardSql);
                detailNode.put("isComplete", isComplete);
                sqlDetailsList.add(detailNode);
            }
        }
        
        return new SqlExtractionResult(validSqls, sqlDetailsList);
    }
    
    /**
     * 安全地获取文本值
     */
    private String getTextValue(JsonNode node, String fieldName, String defaultValue) {
        JsonNode fieldNode = node.get(fieldName);
        return fieldNode != null ? fieldNode.asText(defaultValue) : defaultValue;
    }
    
    /**
     * 安全地获取布尔值
     */
    private boolean getBooleanValue(JsonNode node, String fieldName, boolean defaultValue) {
        JsonNode fieldNode = node.get(fieldName);
        return fieldNode != null ? fieldNode.asBoolean(defaultValue) : defaultValue;
    }
    
    /**
     * 安全地获取整数值
     */
    private int getIntValue(JsonNode node, String fieldName, int defaultValue) {
        JsonNode fieldNode = node.get(fieldName);
        return fieldNode != null ? fieldNode.asInt(defaultValue) : defaultValue;
    }

    /**
     * 构建错误响应
     */
    public String buildErrorResponse(String errorMessage, String fileName, long startTime) {
        try {
            ObjectNode response = objectMapper.createObjectNode();
            response.put("success", false);
            response.put("error", errorMessage);
            response.put("fileName", fileName);
            response.put("timestamp", LocalDateTime.now().toString());
            response.put("analysisTime", System.currentTimeMillis() - startTime);
            return objectMapper.writeValueAsString(response);
        } catch (Exception e) {
            return String.format("{\"success\": false, \"error\": \"%s\", \"fileName\": \"%s\", \"timestamp\": \"%s\"}", 
                    errorMessage, fileName, LocalDateTime.now());
        }
    }
}